CREATE TABLE CUSTOMER(

CustomerID Int NOT NULL,

FirstName Char(25) NOT NULL,

LastName Char(25) NOT NULL,

Phone Char(12) NOT NULL,

EmailAddress Char(100) NULL,

CONSTRAINT CustomerPK PRIMARY KEY(CustomerID));




CREATE TABLE SERVICE(

ServiceID Int NOT NULL,

ServiceDescription Char(50) NOT NULL,

UnitPrice Numeric(8,2) NOT NULL,

CONSTRAINT ServicePK PRIMARY KEY(ServiceID),

CONSTRAINT ServicePrice CHECK
 ((UnitPrice >= 1.50) AND (UnitPrice <= 20.00))
);




CREATE TABLE INVOICE(

InvoiceNumber Int NOT NULL,

CustomerID Int NOT NULL,

DateIn Date NOT NULL,

DateOut Date NULL,

SubTotal Numeric(8,2) NULL,

Tax Numeric(8,2) NULL,

TotalAmount Numeric(8,2) NULL,

CONSTRAINT InvoicePK PRIMARY KEY (InvoiceNumber),

CONSTRAINT Invoice_Cust_FK FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID)

ON UPDATE NO ACTION
 ON DELETE NO ACTION
);



CREATE TABLE INVOICE_ITEM(

InvoiceNumber Int NOT NULL,

ItemNumber Int NOT NULL,

ServiceID Int NOT NULL,

Quantity Int NOT NULL DEFAULT 1,

UnitPrice Numeric(8,2) NULL,

ExtendedPrice Numeric(8,2) NULL,

CONSTRAINT InvoiceItemPK PRIMARY KEY(InvoiceNumber, ItemNumber),

CONSTRAINT Invoice_Item_FK FOREIGN KEY(InvoiceNumber)
 REFERENCES INVOICE(InvoiceNumber)

ON UPDATE CASCADE
 ON DELETE CASCADE,
	
CONSTRAINT Item_ServiceFK FOREIGN KEY(ServiceID) REFERENCES Service(ServiceID)

ON UPDATE CASCADE);



INSERT INTO CUSTOMER VALUES(
100,'Nikki','Kaccaton', '723-543-1233',
'Nikki.Kaccaton@somewhere.com');

INSERT INTO CUSTOMER VALUES(
105,'Brenda','Catnazaro', '723-543-2344',
'Brenda.Catnazaro@somewhere.com');

INSERT INTO CUSTOMER VALUES(
110,'Bruce','LeCat', '723-543-3455',
'Bruce.LeCat@somewhere.com');

INSERT INTO CUSTOMER VALUES(
115,'Betsy','Miller', '725-654-3211',
'Betsy.Miller@somewhere.com');

INSERT INTO CUSTOMER VALUES(
120,'George','Miller', '725-654-4322',
'George.Miller@somewhere.com');

INSERT INTO CUSTOMER VALUES(
125,'Kathy','Miller', '723-514-9877',
'Kathy.Miller@somewhere.com');

INSERT INTO CUSTOMER VALUES(
130,'Betsy','Miller', '723-514-8766',
'Betsy.Miller@elsewhere.com');



INSERT INTO SERVICE VALUES(10,'Men''s Shirt', 1.50);

INSERT INTO SERVICE VALUES(11,'Dress Shirt', 2.50);

INSERT INTO SERVICE VALUES(15,'Women''s Shirt', 1.50);

INSERT INTO SERVICE VALUES(16,'Blouse', 3.50);

INSERT INTO SERVICE VALUES(20,'Slacks-Men''s', 5.00);

INSERT INTO SERVICE VALUES(25,'Slacks-Women''s', 6.00);

INSERT INTO SERVICE VALUES(30,'Skirt', 5.00);

INSERT INTO SERVICE VALUES(31,'Dress Skirt', 6.00);

INSERT INTO SERVICE VALUES(40,'Suit-Men''s', 9.00);

INSERT INTO SERVICE VALUES(45,'Suit-Women''s', 8.50);

INSERT INTO SERVICE VALUES(50,'Tuxedo', 10.00);

INSERT INTO SERVICE VALUES(60,'Formal Gown', 10.00);




INSERT INTO INVOICE VALUES(
2017001, 100,'2017-10-04','2017-010-06',
158.50, 12.52, 171.02);

INSERT INTO INVOICE VALUES(2017002, 105,'2017-10-04','2017-10-06',
25.00, 1.98, 26.98);

INSERT INTO INVOICE VALUES(2017003, 100,'2017-10-06','2017-10-08',49.00, 3.87, 52.87);

INSERT INTO INVOICE VALUES(
2017004, 115,'2017-10-06', '2017-10-08',17.50, 1.38, 18.88);

INSERT INTO INVOICE VALUES(2017005, 125,'2017-10-07','2017-10-11',
12.00, 0.95, 12.95);

INSERT INTO INVOICE VALUES(2017006, 110,'2017-10-11','2017-10-13',152.50, 12.05, 164.55);

INSERT INTO INVOICE VALUES(2017007, 110,'2017-10-11','2017-10-13',7.00, 0.55, 7.55);

INSERT INTO INVOICE VALUES(2017008, 130, '2017-10-12','2017-10-14',140.50, 11.10, 151.60);

INSERT INTO INVOICE VALUES(2017009, 120,'2017-10-12','2017-10-14',
27.00, 2.13, 29.13);
        



INSERT INTO INVOICE_ITEM VALUES(2017001, 1, 16, 2, 3.50, 7.00);

INSERT INTO INVOICE_ITEM VALUES(2017001, 2, 11, 5, 2.50, 12.50);
INSERT INTO INVOICE_ITEM VALUES(2017001, 3, 50, 2, 10.00, 20.00);

INSERT INTO INVOICE_ITEM VALUES(2017001, 4, 20, 10, 5.00, 50.00);

INSERT INTO INVOICE_ITEM VALUES(2017001, 5, 25, 10, 6.00, 60.00);

INSERT INTO INVOICE_ITEM VALUES(2017001, 6, 40, 1, 9.00, 9.00);

INSERT INTO INVOICE_ITEM VALUES(2017002, 1, 11, 10, 2.50, 25.00);

INSERT INTO INVOICE_ITEM VALUES(2017003, 1, 20, 5, 5.00, 25.00);

INSERT INTO INVOICE_ITEM VALUES(2017003, 2, 25, 4, 6.00, 24.00);

INSERT INTO INVOICE_ITEM VALUES(2017004, 1, 11, 7, 2.50, 17.50);

INSERT INTO INVOICE_ITEM VALUES(2017005, 1, 16, 2, 3.50, 7.00);

INSERT INTO INVOICE_ITEM VALUES(2017005, 2, 11, 2, 2.50, 5.00);

INSERT INTO INVOICE_ITEM VALUES(2017006, 1, 16, 5, 3.50, 17.50);

INSERT INTO INVOICE_ITEM VALUES(2017006, 2, 11, 10, 2.50, 25.00);

INSERT INTO INVOICE_ITEM VALUES(2017006, 3, 20, 10, 5.00, 50.00);

INSERT INTO INVOICE_ITEM VALUES(2017006, 4, 25, 10, 6.00, 60.00);

INSERT INTO INVOICE_ITEM VALUES(2017007, 1, 16, 2, 3.50, 7.00);

INSERT INTO INVOICE_ITEM VALUES(2017008, 1, 16, 3, 3.50, 10.50);

INSERT INTO INVOICE_ITEM VALUES(2017008, 2, 11, 12, 2.50, 30.00);

INSERT INTO INVOICE_ITEM VALUES(2017008, 3, 20, 8, 5.00, 40.00);

INSERT INTO INVOICE_ITEM VALUES(2017008, 4, 25, 10, 6.00, 60.00);

INSERT INTO INVOICE_ITEM VALUES(2017009, 1, 40, 3, 9.00, 27.00);





/* Additional data created by Dr. Paradice */



INSERT INTO CUSTOMER VALUES(
200,'Karen','Kart', '223-543-1233',
'Karen.Kart@somewhere.com');

INSERT INTO CUSTOMER VALUES(
205,'Celia','Isaro', '223-543-2344',
'Celia.Isaro@somewhere.com');

INSERT INTO CUSTOMER VALUES(
210,'Tom','LePupp', '223-543-3455',
'Tom.LePupp@somewhere.com');

INSERT INTO CUSTOMER VALUES(
215,'Renee','Ewart', '225-654-3211',
'Renee.Ewart@somewhere.com');

INSERT INTO CUSTOMER VALUES(
220,'Bill','Fuerst', '225-654-4322',
'Bill.Fuerst@somewhere.com');

INSERT INTO CUSTOMER VALUES(
225,'Donte','Self', '223-514-9877',
'Donte.Self@somewhere.com');

INSERT INTO CUSTOMER VALUES(
230,'Juan','Gomez', '223-514-8766',
'Juan.Gomez@elsewhere.com');



INSERT INTO SERVICE VALUES(12,'Men''s Sweater', 3.50);

INSERT INTO SERVICE VALUES(13,'Dress Shirt Formal', 3.50);

INSERT INTO SERVICE VALUES(18,'Women''s Shirt Formal', 2.50);

INSERT INTO SERVICE VALUES(19,'Blouse Ruffles', 4.50);

INSERT INTO SERVICE VALUES(21,'Slacks-Men''s Chino', 5.50);

INSERT INTO SERVICE VALUES(26,'Slacks-Women''s Linen', 7.00);

INSERT INTO SERVICE VALUES(32,'Skirt Extra Pleats', 6.00);

INSERT INTO SERVICE VALUES(33,'Dress Skirt Long', 7.00);

INSERT INTO SERVICE VALUES(41,'Suit-Men''s Wool', 10.00);

INSERT INTO SERVICE VALUES(46,'Suit-Women''s Wool', 10.50);

INSERT INTO SERVICE VALUES(51,'Tuxedo Non-Black', 14.00);

INSERT INTO SERVICE VALUES(61,'Formal Gown w Lace', 16.00);

INSERT INTO SERVICE VALUES(62,'Formal Gown w Sequins', 20.00);




INSERT INTO INVOICE VALUES(
2017011, 100,'2017-10-24','2017-10-26',
184.50, 14.76, 199.26);

INSERT INTO INVOICE VALUES(2017012, 205,'2017-10-24','2017-10-26',
30.50, 2.44, 32.94);

INSERT INTO INVOICE VALUES(2017013, 100,'2017-10-26','2017-10-28',54.00, 4.32, 54.32);

INSERT INTO INVOICE VALUES(
2017014, 215,'2017-10-26','2017-10-28',16.00, 1.28, 17.28);

INSERT INTO INVOICE VALUES(2017015, 125,'2017-10-27','2017-10-29',
86.00, 6.88, 92.88);

INSERT INTO INVOICE VALUES(2017016, 110,'2017-10-21','2017-10-23',112.50, 9.00, 121.50);

INSERT INTO INVOICE VALUES(2017017, 110,'2017-10-21','2017-10-23',7.00, 0.56, 7.56);

INSERT INTO INVOICE VALUES(2017018, 230,'2017-10-22','2017-10-24',144.50, 11.56, 156.06);

INSERT INTO INVOICE VALUES(2017019, 120,'2017-10-22','2017-10-24',
27.00, 2.16, 29.16);
        



INSERT INTO INVOICE_ITEM VALUES(2017011, 1, 26, 2, 7.00, 14.00);

INSERT INTO INVOICE_ITEM VALUES(2017011, 2, 21, 5, 5.50, 27.50);
INSERT INTO INVOICE_ITEM VALUES(2017011, 3, 51, 2, 14.00, 28.00);

INSERT INTO INVOICE_ITEM VALUES(2017011, 4, 33, 10, 7.00, 70.00);

INSERT INTO INVOICE_ITEM VALUES(2017011, 5, 19, 10, 4.50, 45.00);

INSERT INTO INVOICE_ITEM VALUES(2017012, 1, 41, 2, 10.00, 20.00);

INSERT INTO INVOICE_ITEM VALUES(2017012, 2, 46, 1, 10.50, 10.50);

INSERT INTO INVOICE_ITEM VALUES(2017013, 1, 32, 5, 6.00, 30.00);

INSERT INTO INVOICE_ITEM VALUES(2017013, 2, 25, 4, 6.00, 24.00);

INSERT INTO INVOICE_ITEM VALUES(2017014, 1, 61, 1, 16.00, 16.00);

INSERT INTO INVOICE_ITEM VALUES(2017015, 1, 26, 2, 7.00, 14.00);

INSERT INTO INVOICE_ITEM VALUES(2017015, 2, 32, 2, 6.00, 12.00);

INSERT INTO INVOICE_ITEM VALUES(2017015, 3, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2017016, 1, 16, 5, 3.50, 17.50);

INSERT INTO INVOICE_ITEM VALUES(2017016, 2, 19, 10, 4.50, 45.00);

INSERT INTO INVOICE_ITEM VALUES(2017016, 3, 20, 10, 5.00, 50.00);

INSERT INTO INVOICE_ITEM VALUES(2017017, 1, 16, 2, 3.50, 7.00);
INSERT INTO INVOICE_ITEM VALUES(2017018, 1, 16, 3, 3.50, 10.50);

INSERT INTO INVOICE_ITEM VALUES(2017018, 2, 11, 12, 2.50, 30.00);

INSERT INTO INVOICE_ITEM VALUES(2017018, 3, 21, 8, 5.50, 44.00);

INSERT INTO INVOICE_ITEM VALUES(2017018, 4, 25, 10, 6.00, 60.00);
INSERT INTO INVOICE_ITEM VALUES(2017019, 1, 62, 2, 20.00, 40.00);